﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Runda.CMS.Common.Utility;
using System.Data.Common;
using System.Data;
using Runda.CMS.Common.Entity;

namespace Runda.CMS.Dal
{
   public partial  class BGSalesBudgetsDal:DalBase
   {
       public DataTable GetSalesBudgets(string WhereClause)
        {
            string sql = @"select * from (
select a.*,left(a.memo,20) as Memo_Short,b.categoryName,c.customerName as SalesAgencyName,c1.customerName from Cms_BG_SalesBudgets a join Cms_Ctrt_InstrumentCategory b on a.ProductCategoryID = b.categoryGUID
left join Cms_Ctrt_Customer c on a.SalesAgencyID = c.CustomerID
left join Cms_Ctrt_Customer c1 on a.customerid = c1.CustomerID
) T where 1=1 {0}";

           sql = string.Format(sql,WhereClause);

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           return CurrentDatabase.ExecuteDataSet(dbcommand).Tables[0];
        }

       public DataTable QuerySalesBudgets(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
       {
           StringBuilder sqlSb = new StringBuilder(@"select * from 
(select a.*,left(a.memo,20) as Memo_Short,case when a.salesBudgetType='1' then '销售预算' else '投放预算' end as SalesBudgetTypeName,b.customerName,dept.DepartmentName as companyName,b1.CustomerName as SalesAgencyName,ic.categoryName,u.UserName as SalesName,u.DepartmentName
 from Cms_BG_SalesBudgets a 
inner join Cms_Ctrt_Customer b on a.CustomerID = b.CustomerID
left join  vw_sysUser u on u.UserName = a.sales
left join  Cms_Ctrt_Customer b1 on a.SalesAgencyID = b1.CustomerID
inner join Cms_Sys_Department dept on dept.DepartmentCode = a.company and dept.isCompany = '1'
inner join Cms_Ctrt_InstrumentCategory ic on ic.categoryGUID = a.ProductCategoryID) 
AS Z where 1=1 {0}");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

           CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

           DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
           CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
           CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
           RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
           return ds.Tables[0];
       }
       public DataTable QueryCustomerSalesBudget_Rpt(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	CustomerName
    ,max(Age) as Age
	,sum(m01Amount) as m01Amount
	,sum(m02Amount) as m02Amount
	,sum(m03Amount) as m03Amount
	,sum(m04Amount) as m04Amount
	,sum(m05Amount) as m05Amount
	,sum(m06Amount) as m06Amount
	,sum(m07Amount) as m07Amount
	,sum(m08Amount) as m08Amount
	,sum(m09Amount) as m09Amount
	,sum(m10Amount) as m10Amount
	,sum(m11Amount) as m11Amount
	,sum(m12Amount) as m12Amount
    ,sum(totalAmount) as totalAmount
	
from
(
select a.BudgetYear,c.CustomerName,v.*,a.totalAmount,a.company,a.CostType,a.sales,a.Producter,a.ProductManager,a.productCategoryCode,a.ProductCategoryID,a.CustomerID,va.Age from Cms_BG_SalesBudgets a
inner join Cms_Ctrt_Customer c on a.customerID = c.customerID
inner join vw_SalesBudgetForAmountByMonth v on v.BudgetID = a.BudgetID
left join vw_CustomerAge_all va on va.customerCode = c.CustomerCode  and va.company = a.company
) z 
where 1=1 {0}
group by CustomerName");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

           CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

           DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
           CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
           CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
           RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
           return ds.Tables[0];
       }

       public DataTable QueryCustomer2SalesBudget_Rpt(string WhereClause, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	CustomerName
    ,AgencyName
    ,max(Age) as Age
	,sum(m01Amount) as m01Amount
	,sum(m02Amount) as m02Amount
	,sum(m03Amount) as m03Amount
	,sum(m04Amount) as m04Amount
	,sum(m05Amount) as m05Amount
	,sum(m06Amount) as m06Amount
	,sum(m07Amount) as m07Amount
	,sum(m08Amount) as m08Amount
	,sum(m09Amount) as m09Amount
	,sum(m10Amount) as m10Amount
	,sum(m11Amount) as m11Amount
	,sum(m12Amount) as m12Amount
    ,sum(totalAmount) as totalAmount
	
from
(
select a.BudgetYear,c.CustomerName,ac.CustomerName as AgencyName,v.*,a.totalAmount,a.company,a.CostType,a.sales,a.Producter,a.ProductManager,a.productCategoryCode,a.ProductCategoryID,a.CustomerID,va.Age from Cms_BG_SalesBudgets a
inner join Cms_Ctrt_Customer c on a.customerID = c.customerID
left join Cms_Ctrt_Customer ac on a.SalesAgencyID = ac.customerID
inner join vw_SalesBudgetForAmountByMonth v on v.BudgetID = a.BudgetID
left join vw_CustomerAge_all va on va.customerCode = c.CustomerCode  and va.company = a.company
) z 
where 1=1 {0}
group by CustomerName,AgencyName");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           string CmdText = @"WITH T AS
(
SELECT ROW_NUMBER() OVER(ORDER BY {2} ) AS row_number, * 
  FROM ({0}) as A
)
SELECT * from T WHERE row_number > @StartRowNum AND  row_number <= @EndRowNum
select count(*) from ({1}) as B 
";

           CmdText = string.Format(CmdText, sqlStr, sqlStr, OrderBy);

           DbCommand dbCommand = this._db.GetSqlStringCommand(CmdText);
           CurrentDatabase.AddInParameter(dbCommand, "@StartRowNum", System.Data.DbType.Int32, (PageIndex - 1) * PageSize);
           CurrentDatabase.AddInParameter(dbCommand, "@EndRowNum", System.Data.DbType.Int32, PageIndex * PageSize);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);
           RecordCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);
           return ds.Tables[0];
       }

       public DataTable QueryInventoryPutPlan_Rpt(string WhereClause, string OrderBy)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	BudgetYear,categoryName,productModel,PutCost
	,sum(m01quantity) as m01quantity
	,sum(m02quantity) as m02quantity
	,sum(m03quantity) as m03quantity
	,sum(m04quantity) as m04quantity
	,sum(m05quantity) as m05quantity
	,sum(m06quantity) as m06quantity
	,sum(m07quantity) as m07quantity
	,sum(m08quantity) as m08quantity
	,sum(m09quantity) as m09quantity
	,sum(m10quantity) as m10quantity
	,sum(m11quantity) as m11quantity
	,sum(m12quantity) as m12quantity
    ,sum(totalquantity) as Totalquantity
	,SUM(totalquantity * PutCost) as TotalPutCost
,sum(m01quantity)*isnull(PutCost,0) as m01Amount
	,sum(m02quantity)*isnull(PutCost,0) as m02Amount
	,sum(m03quantity)*isnull(PutCost,0) as m03Amount
	,sum(m04quantity)*isnull(PutCost,0) as m04Amount
	,sum(m05quantity)*isnull(PutCost,0) as m05Amount
	,sum(m06quantity)*isnull(PutCost,0) as m06Amount
	,sum(m07quantity)*isnull(PutCost,0)as m07Amount
	,sum(m08quantity)*isnull(PutCost,0) as m08Amount
	,sum(m09quantity)*isnull(PutCost,0) as m09Amount
	,sum(m10quantity)*isnull(PutCost,0) as m10Amount
	,sum(m11quantity)*isnull(PutCost,0) as m11Amount
	,sum(m12quantity)*isnull(PutCost,0) as m12Amount
from
(
select a.BudgetYear,i.categoryName,a.productModel,b.PutCost,v.*,a.TotalQuantity,a.company,a.CostType,a.sales,a.Producter,a.ProductManager,a.productCategoryCode from Cms_BG_SalesBudgets a
inner join Cms_Wsm_InstrumentInfo wi on wi.instrumentName = a.productModel
inner join Cms_Ctrt_InstrumentCategory i on wi.CategoryGUID = i.categoryGUID
inner join Cms_BG_InstrumentPutList b on a.BudgetYear = b.CostYear and a.productModel = b.InstrumentName and a.company = b.co
inner join vw_SalesBudgetForQuantityByMonth v on v.BudgetID = a.BudgetID
where a.salesBudgetType = 2
) z 
where 1=1 {0}
group by BudgetYear,categoryName,productModel,PutCost");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable ExportInventoryPutPlan_Rpt(string WhereClause, string OrderBy)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	BudgetYear as [投放年份],categoryName as [产品大类],productModel as [仪器型号]
    ,SUM(totalquantity * PutCost) as [投放总额],MAX(PutCost) as  [采购单价]
    ,sum(m01quantity)*isnull(MAX(PutCost),0) as [1月份金额]
	,sum(m02quantity)*isnull(MAX(PutCost),0) as [2月份金额]
	,sum(m03quantity)*isnull(MAX(PutCost),0) as [3月份金额]
	,sum(m04quantity)*isnull(MAX(PutCost),0) as [4月份金额]
	,sum(m05quantity)*isnull(MAX(PutCost),0) as [5月份金额]
	,sum(m06quantity)*isnull(MAX(PutCost),0) as [6月份金额]
	,sum(m07quantity)*isnull(MAX(PutCost),0) as [7月份金额]
	,sum(m08quantity)*isnull(MAX(PutCost),0) as [8月份金额]
	,sum(m09quantity)*isnull(MAX(PutCost),0) as [9月份金额]
	,sum(m10quantity)*isnull(MAX(PutCost),0) as [10月份金额]
	,sum(m11quantity)*isnull(MAX(PutCost),0) as [11月份金额]
	,sum(m12quantity)*isnull(MAX(PutCost),0) as [12月份金额]
	,sum(m01quantity) as [1月份数量]
	,sum(m02quantity) as [2月份数量]
	,sum(m03quantity) as [3月份数量]
	,sum(m04quantity) as [4月份数量]
	,sum(m05quantity) as [5月份数量]
	,sum(m06quantity) as [6月份数量]
	,sum(m07quantity) as [7月份数量]
	,sum(m08quantity) as [8月份数量]
	,sum(m09quantity) as [9月份数量]
	,sum(m10quantity) as [10月份数量]
	,sum(m11quantity) as [11月份数量]
	,sum(m12quantity) as [12月份数量]
    ,sum(totalquantity) as [全年总数量]
	

from
(
select a.BudgetYear,i.categoryName,a.productModel,b.PutCost,v.*,a.TotalQuantity,a.CostType,a.company,a.sales,a.Producter,a.ProductManager,a.productCategoryCode from Cms_BG_SalesBudgets a
inner join Cms_Wsm_InstrumentInfo wi on wi.instrumentName = a.productModel
inner join Cms_Ctrt_InstrumentCategory i on wi.CategoryGUID = i.categoryGUID
inner join Cms_BG_InstrumentPutList b on a.BudgetYear = b.CostYear and a.productModel = b.InstrumentName and a.company = b.co
inner join vw_SalesBudgetForQuantityByMonth v on v.BudgetID = a.BudgetID
where a.salesBudgetType = 2
) z 
where 1=1 {0}
group by BudgetYear,categoryName,productModel");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable CalcInventoryPutPlan_Rpt(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	BudgetYear,count(distinct categoryName) as categoryName,count(distinct productModel) as productModel
	,sum(m01quantity) as m01quantity
	,sum(m02quantity) as m02quantity
	,sum(m03quantity) as m03quantity
	,sum(m04quantity) as m04quantity
	,sum(m05quantity) as m05quantity
	,sum(m06quantity) as m06quantity
	,sum(m07quantity) as m07quantity
	,sum(m08quantity) as m08quantity
	,sum(m09quantity) as m09quantity
	,sum(m10quantity) as m10quantity
	,sum(m11quantity) as m11quantity
	,sum(m12quantity) as m12quantity
    ,sum(totalquantity) as Totalquantity
	,SUM(totalquantity * PutCost) as TotalPutCost
    ,sum(m01quantity)*isnull(MAX(PutCost),0) as m01Amount
	,sum(m02quantity)*isnull(MAX(PutCost),0) as m02Amount
	,sum(m03quantity)*isnull(MAX(PutCost),0) as m03Amount
	,sum(m04quantity)*isnull(MAX(PutCost),0) as m04Amount
	,sum(m05quantity)*isnull(MAX(PutCost),0) as m05Amount
	,sum(m06quantity)*isnull(MAX(PutCost),0) as m06Amount
	,sum(m07quantity)*isnull(MAX(PutCost),0) as m07Amount
	,sum(m08quantity)*isnull(MAX(PutCost),0) as m08Amount
	,sum(m09quantity)*isnull(MAX(PutCost),0) as m09Amount
	,sum(m10quantity)*isnull(MAX(PutCost),0) as m10Amount
	,sum(m11quantity)*isnull(MAX(PutCost),0) as m11Amount
	,sum(m12quantity)*isnull(MAX(PutCost),0) as m12Amount
from
(
select a.BudgetYear,i.categoryName,a.productModel,b.PutCost,v.*,a.TotalQuantity,a.CostType,a.company,a.sales,a.Producter,a.ProductManager,a.productCategoryCode from Cms_BG_SalesBudgets a
inner join Cms_Wsm_InstrumentInfo wi on wi.instrumentName = a.productModel
inner join Cms_Ctrt_InstrumentCategory i on wi.CategoryGUID = i.categoryGUID
inner join Cms_BG_InstrumentPutList b on a.BudgetYear = b.CostYear and a.productModel = b.InstrumentName and a.company = b.co
inner join vw_SalesBudgetForQuantityByMonth v on v.BudgetID = a.BudgetID
where a.salesBudgetType = 2
) z 
where 1=1 {0}
group by BudgetYear");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable CalcCustomerSalesBudget_Rpt(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	count(distinct CustomerName) as customerNumber
	,sum(m01Amount) as m01Amount
	,sum(m02Amount) as m02Amount
	,sum(m03Amount) as m03Amount
	,sum(m04Amount) as m04Amount
	,sum(m05Amount) as m05Amount
	,sum(m06Amount) as m06Amount
	,sum(m07Amount) as m07Amount
	,sum(m08Amount) as m08Amount
	,sum(m09Amount) as m09Amount
	,sum(m10Amount) as m10Amount
	,sum(m11Amount) as m11Amount
	,sum(m12Amount) as m12Amount
    ,sum(totalAmount) as totalAmount
	
from
(
select a.BudgetYear,c.CustomerName,v.*,a.TotalQuantity,a.company,a.CostType,a.sales,a.Producter,a.ProductManager,a.productCategoryCode,a.TotalAmount,a.ProductCategoryID,a.CustomerID from Cms_BG_SalesBudgets a
inner join Cms_Ctrt_Customer c on a.customerID = c.customerID
inner join vw_SalesBudgetForAmountByMonth v on v.BudgetID = a.BudgetID
) z 
where 1=1 {0} ");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable ExportCustomerSalesBudget_Rpt(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select
	CustomerName as [客户]
    ,max(Age) as [账龄]
    ,sum(totalAmount) as [预算总额]
	,sum(m01Amount) as [1月]
	,sum(m02Amount) as [2月]
	,sum(m03Amount) as [3月]
	,sum(m04Amount) as [4月]
	,sum(m05Amount) as [5月]
	,sum(m06Amount) as [6月]
	,sum(m07Amount) as [7月]
	,sum(m08Amount) as [8月]
	,sum(m09Amount) as [9月]
	,sum(m10Amount) as [10月]
	,sum(m11Amount) as [11月]
	,sum(m12Amount) as [12月]
    
	
from
(
select a.BudgetYear,c.CustomerName,v.*,a.TotalQuantity,a.company,a.CostType,a.sales,a.Producter,a.ProductManager,a.productCategoryCode,a.TotalAmount,a.ProductCategoryID,a.CustomerID,va.Age from Cms_BG_SalesBudgets a
inner join Cms_Ctrt_Customer c on a.customerID = c.customerID
inner join vw_SalesBudgetForAmountByMonth v on v.BudgetID = a.BudgetID
left join vw_CustomerAge_all va on va.customerCode = c.CustomerCode and va.company = a.company
) z 
where 1=1 {0}
group by CustomerName order by CustomerName ");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);

           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public void UpdateByAssistID(BGSalesBudgetsEntity entity)
       {
           string sql = @"update Cms_BG_SalesBudgets set 
BudgetYear=@BudgetYear,company=@company,customerID=@customerID,CustomerIsTrue=@CustomerIsTrue,Producter=@Producter,sales=@sales,biller=@biller,MemoEx=@Memo,ProductManager=@ProductManager
WHERE AssistID=@AssistID";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@AssistID", DbType.Guid, entity.AssistID);
           CurrentDatabase.AddInParameter(dbcommand, "@BudgetYear", DbType.String, entity.BudgetYear);
           CurrentDatabase.AddInParameter(dbcommand, "@company", DbType.String, entity.company);
           CurrentDatabase.AddInParameter(dbcommand, "@customerID", DbType.Guid, entity.customerID);
           CurrentDatabase.AddInParameter(dbcommand, "@CustomerIsTrue", DbType.String, entity.CustomerIsTrue);
           CurrentDatabase.AddInParameter(dbcommand, "@Producter", DbType.String, entity.Producter);
           CurrentDatabase.AddInParameter(dbcommand, "@sales", DbType.String, entity.sales);
           CurrentDatabase.AddInParameter(dbcommand, "@biller", DbType.String, entity.biller);
           CurrentDatabase.AddInParameter(dbcommand, "@Memo", DbType.String, entity.MemoEx);
           CurrentDatabase.AddInParameter(dbcommand, "@ProductManager", DbType.String, entity.ProductManager);

           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }


       public DataTable CalcbillZKAmount(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select sum(iNatSum) as iNatSum from (select a.*,c.customerName from vw_Salebills_Zk a
inner join cms_Ctrt_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
where 1=1 {0}) s");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);



           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable CalcbillAmount(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select sum(iNatMoney) as iNatMoney,sum(iNatSum) as iNatSum from Cms_BG_Bill a
inner join cms_Ctrt_Customer c with(nolock) on c.CustomerCode = a.cCusCode and a.BillUnit = c.company
where 1=1 {0}");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);



           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable CalcbillAmountEx(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select isnull(sum(iNatMoney),0) as iNatMoney,isnull(sum(iNatSum),0) as iNatSum,isnull(sum(SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4),0) as TotalSaleAmount,isnull(sum(Cost+Cost2+Cost3),0) as TotalCost,isnull((sum(SaleAmount+SaleAmount1+SaleAmount2+SaleAmount3+SaleAmount4) -sum(Cost+Cost3+Cost2)),0) as Totalrevenue  from vw_BG_Bill_query
where 1=1 {0}");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);



           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable CalcSalesBudgets(string WhereClause)
       {
           StringBuilder sqlSb = new StringBuilder(@"select sum(isnull(Q1quantity,0)) as sumQ1quantity,sum(isnull(Q1Amount,0)) as sumQ1Amount,sum(isnull(Q1Cost,0)) as sumQ1Cost,sum(isnull(Q1RentAmount,0)) as sumQ1RentAmount
    ,sum(isnull(Q2quantity,0)) as sumQ2quantity,sum(isnull(Q2Amount,0)) as sumQ2Amount,sum(isnull(Q2Cost,0)) as sumQ2Cost,sum(isnull(Q2RentAmount,0)) as sumQ2RentAmount
    ,sum(isnull(Q3quantity,0)) as sumQ3quantity,sum(isnull(Q3Amount,0)) as sumQ3Amount,sum(isnull(Q3Cost,0)) as sumQ3Cost,sum(isnull(Q3RentAmount,0)) as sumQ3RentAmount
    ,sum(isnull(Q4quantity,0)) as sumQ4quantity,sum(isnull(Q4Amount,0)) as sumQ4Amount,sum(isnull(Q4Cost,0)) as sumQ4Cost,sum(isnull(Q4RentAmount,0)) as sumQ4RentAmount
    ,sum(TotalAmount) as TotalAmount,sum(TotalRentAmount) as TotalRentAmount
from 
(select a.*,case when a.salesBudgetType='1' then '销售预算' else '投放预算' end as SalesBudgetTypeName,b.customerName,dept.DepartmentName as companyName,b.CustomerName as SalesAgencyName,ic.categoryName
from Cms_BG_SalesBudgets a 
inner join Cms_Ctrt_Customer b on a.CustomerID = b.CustomerID
left join  Cms_Ctrt_Customer b1 on a.SalesAgencyID = b1.CustomerID
inner join Cms_Sys_Department dept on dept.DepartmentCode = a.company and dept.isCompany = '1'
inner join Cms_Ctrt_InstrumentCategory ic on ic.categoryGUID = a.ProductCategoryID) 
AS Z where 1=1 {0}");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause);



           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public DataTable ExportSalesBudgets(string WhereClause,string orderBy)
       {
           StringBuilder sqlSb = new StringBuilder(@"select BudgetYear as [预算年份]
,CompanyName as [公司]
,customerName as [客户]
,CustomerIsTrue as [确认]
,SalesAgencyName as [经销商]
,SalesAgencyIsTrue as [确认]
,ProductManager as [产品经理]
,Producter as [产品专员]
,DepartmentName as [销售组]
,SalesName as [销售责任人]
,SalesBudgetTypeName as [预算类型]
,categoryname as [大类]
,productModel as [仪器型号]
,TotalAmount as [总金额]
,unitPrice as [单价]
,Q1Amount as [Q1金额]
,Q2Amount as [Q2金额]
,Q3Amount as [Q3金额]
,Q4Amount as [Q4金额]
,Q1quantity as [Q1数量]
,Q2quantity as [Q2数量]
,Q3quantity as [Q3数量]
,Q4quantity as [Q4数量]
,TotalQuantity as [总数量]
,Q1RentAmount as [Q1租金]
,Q2RentAmount as [Q2租金]
,Q3RentAmount as [Q3租金]
,Q4RentAmount as [租金]
,TotalRentAmount as [总租金]

from 
(select a.*,case when a.salesBudgetType='1' then '销售预算' else '投放预算' end as SalesBudgetTypeName,b.customerName,dept.DepartmentName as companyName,b1.CustomerName as SalesAgencyName,ic.categoryName,u.UserName as SalesName,u.DepartmentName
from Cms_BG_SalesBudgets a 
inner join Cms_Ctrt_Customer b on a.CustomerID = b.CustomerID
left join  vw_sysUser u on u.UserName = a.sales
left join  Cms_Ctrt_Customer b1 on a.SalesAgencyID = b1.CustomerID
inner join Cms_Sys_Department dept on dept.DepartmentCode = a.company and dept.isCompany = '1'
inner join Cms_Ctrt_InstrumentCategory ic on ic.categoryGUID = a.ProductCategoryID) 
AS Z where 1=1 {0} order by {1} ");
           string sqlStr = string.Format(sqlSb.ToString(), WhereClause,orderBy);



           DbCommand dbCommand = this._db.GetSqlStringCommand(sqlStr);

           DataSet ds = CurrentDatabase.ExecuteDataSet(dbCommand);

           return ds.Tables[0];
       }

       public List<WsmInstrumentInfoEntity> GetInstrumentEntities(Guid CategoryID)
       {
           List<WsmInstrumentInfoEntity> entities = null;
           string sql = @"with CTE as
(
select * from Cms_Ctrt_InstrumentCategory where categoryGUID = @CategoryID

union all

select A.* from Cms_Ctrt_InstrumentCategory A join CTE on A.parentGUID = CTE.categoryGUID
)

select * from Cms_Wsm_InstrumentInfo where categoryGUID in (select categoryGUID from  CTE )";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@CategoryID", System.Data.DbType.Guid, CategoryID);
           using (IDataReader reader = CurrentDatabase.ExecuteReader(dbcommand))
           {
               entities = GetListFromReader<WsmInstrumentInfoEntity>(reader);
           }
           return entities;
       }

       public List<WsmInstrumentInfoEntity> GetInstrumentEntities(string Code)
       {
           List<WsmInstrumentInfoEntity> entities = null;
           string sql = @"select * from Cms_Wsm_InstrumentInfo where 
instrumentCode like @code
or instrumentName like @code
or categoryGUID in (select categoryGUID from Cms_Ctrt_InstrumentCategory where categoryCode like @code)
";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@code", System.Data.DbType.String, "%" + Code + "%");
           using (IDataReader reader = CurrentDatabase.ExecuteReader(dbcommand))
           {
               entities = GetListFromReader<WsmInstrumentInfoEntity>(reader);
           }
           return entities;
       }

       public void SubmmitBudget(string bgYear,string Operator)
       {
           string sql = @"update Cms_BG_SalesBudgets set 
BudgetSubmmitStatus=1 WHERE ProductManager=@ProductManager and BudgetYear=@bgYear;update cms_BG_SalesBudgetProcessList set SubmmitStatus = 1,SubmitTime=getdate()  WHERE ProductManager=@ProductManager and BudgetYear=@bgYear";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@ProductManager", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@bgYear", DbType.String, bgYear);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void cancelDataImportStatus(Guid closeGUID)
       {
           string sql = @"update cms_sys_CloseAccount set 
isLock=0 WHERE CloseGUID=@closeGUID";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@closeGUID", DbType.Guid, closeGUID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }
       public void cancelDataAdjustStatus(Guid closeGUID)
       {
           string sql = @"update cms_sys_CloseAccount set 
isAdjust=0 WHERE CloseGUID=@closeGUID";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@closeGUID", DbType.Guid, closeGUID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }
       public void cancelSaleDataAdjustStatus(Guid closeGUID)
       {
           string sql = @"update cms_sys_CloseAccount set 
isSaleAdjust=0 WHERE CloseGUID=@closeGUID";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@closeGUID", DbType.Guid, closeGUID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void cancelDataCloseStatus(Guid closeGUID)
       {
           string sql = @"update cms_sys_CloseAccount set 
isClose=0 WHERE CloseGUID=@closeGUID";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@closeGUID", DbType.Guid, closeGUID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }


       public void setDataStatus(string sYear,string sMonth,string company,string lockStatus,string Operator,DateTime OperateDate)
       {
           string sql = @"update cms_sys_CloseAccount set 
isLock=@lockStatus,dDataLockdate=@OperateDate,DataLockOperator=@Operator WHERE company=@company and iYear=@sYear and iMonth=@sMonth;";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@Operator", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@company", DbType.String, company);
           CurrentDatabase.AddInParameter(dbcommand, "@sYear", DbType.String, sYear);
           CurrentDatabase.AddInParameter(dbcommand, "@sMonth", DbType.String, sMonth);
           CurrentDatabase.AddInParameter(dbcommand, "@lockStatus", DbType.String, lockStatus);
           CurrentDatabase.AddInParameter(dbcommand, "@OperateDate", DbType.DateTime, OperateDate);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void setDataAdjustStatus(string sYear, string sMonth, string company, string lockStatus, string Operator, DateTime OperateDate)
       {
           string sql = @"update cms_sys_CloseAccount set 
isAdjust=@lockStatus,dAdjustLockdate=@OperateDate,AdjustOpertor=@Operator WHERE company=@company and iYear=@sYear and iMonth=@sMonth;";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@Operator", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@company", DbType.String, company);
           CurrentDatabase.AddInParameter(dbcommand, "@sYear", DbType.String, sYear);
           CurrentDatabase.AddInParameter(dbcommand, "@sMonth", DbType.String, sMonth);
           CurrentDatabase.AddInParameter(dbcommand, "@lockStatus", DbType.String, lockStatus);
           CurrentDatabase.AddInParameter(dbcommand, "@OperateDate", DbType.DateTime, OperateDate);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void setSaleDataAdjustStatus(string sYear, string sMonth, string company, string lockStatus, string Operator, DateTime OperateDate)
       {
           string sql = @"update cms_sys_CloseAccount set 
isSaleAdjust=@lockStatus,dSaleAdjustLockDate=@OperateDate,SaleAdjustOperator=@Operator WHERE company=@company and iYear=@sYear and iMonth=@sMonth;";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@Operator", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@company", DbType.String, company);
           CurrentDatabase.AddInParameter(dbcommand, "@sYear", DbType.String, sYear);
           CurrentDatabase.AddInParameter(dbcommand, "@sMonth", DbType.String, sMonth);
           CurrentDatabase.AddInParameter(dbcommand, "@lockStatus", DbType.String, lockStatus);
           CurrentDatabase.AddInParameter(dbcommand, "@OperateDate", DbType.DateTime, OperateDate);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void setDataCloseStatus(string sYear, string sMonth, string company, string lockStatus, string Operator, DateTime OperateDate)
       {
           string sql = @"update cms_sys_CloseAccount set 
isClose=@lockStatus,dClosedate=@OperateDate,CloseOperator=@Operator WHERE company=@company and iYear=@sYear and iMonth=@sMonth;";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@Operator", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@company", DbType.String, company);
           CurrentDatabase.AddInParameter(dbcommand, "@sYear", DbType.String, sYear);
           CurrentDatabase.AddInParameter(dbcommand, "@sMonth", DbType.String, sMonth);
           CurrentDatabase.AddInParameter(dbcommand, "@lockStatus", DbType.String, lockStatus);
           CurrentDatabase.AddInParameter(dbcommand, "@OperateDate", DbType.DateTime, OperateDate);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }



       public void RejectBudget(string ProcessID)
       {
           string sql = @"declare @ProductManager nvarchar(20),@BudgetYear char(4) select @ProductManager = ProductManager,@BudgetYear = BudgetYear  from cms_BG_SalesBudgetProcessList where ProcessID = @ProcessID; update Cms_BG_SalesBudgets set 
BudgetSubmmitStatus=0 WHERE ProductManager=@ProductManager and BudgetYear=@BudgetYear;update cms_BG_SalesBudgetProcessList set SubmmitStatus = 0,SubmitTime=null  WHERE ProcessID = @ProcessID";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@ProcessID", DbType.String, ProcessID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void CheckBudget(string ProcessID, string Operator)
       {
           string sql = @"declare @ProductManager nvarchar(20),@BudgetYear char(4) select @ProductManager = ProductManager,@BudgetYear = BudgetYear  from cms_BG_SalesBudgetProcessList where ProcessID = @ProcessID;update Cms_BG_SalesBudgets set 
BudgetCheckStatus=1 WHERE BudgetYear=@BudgetYear and ProductManager=@ProductManager;update cms_BG_SalesBudgetProcessList set CheckStatus = 1,CheckTime=getdate(),checker = @checker  WHERE BudgetYear=@BudgetYear and ProductManager=@ProductManager";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@checker", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@ProcessID", DbType.String, ProcessID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public void ReturnBudget(string ProcessID, string Operator)
       {
           string sql = @"declare @ProductManager nvarchar(20),@BudgetYear char(4) select @ProductManager = ProductManager,@BudgetYear = BudgetYear  from cms_BG_SalesBudgetProcessList where ProcessID = @ProcessID;update Cms_BG_SalesBudgets set 
BudgetCheckStatus=0,BudgetSubmmitStatus=0 WHERE BudgetYear=@BudgetYear and ProductManager=@ProductManager;update cms_BG_SalesBudgetProcessList set CheckStatus = 0, SubmmitStatus = 0,CheckTime=null  WHERE BudgetYear=@BudgetYear and ProductManager=@ProductManager";

           DbCommand dbcommand = CurrentDatabase.GetSqlStringCommand(sql);
           CurrentDatabase.AddInParameter(dbcommand, "@checker", DbType.String, Operator);
           CurrentDatabase.AddInParameter(dbcommand, "@ProcessID", DbType.String, ProcessID);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
       }

       public string GenReportDataByProduct(string sqlwhere)
       {
           DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("GenReportDataByProduct");
           CurrentDatabase.AddInParameter(dbcommand, "@sqlwhere", DbType.String, sqlwhere);
           CurrentDatabase.AddOutParameter(dbcommand, "@tblname", DbType.String, 30);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
           return dbcommand.Parameters["@tblname"].Value.ToString();
       }

       public void syncUF2Budget(string budgetYearMonth, string company)
       {
           DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("syncBills");
           dbcommand.CommandTimeout = 0;
           CurrentDatabase.AddInParameter(dbcommand, "@budgetYearMonth", DbType.String, budgetYearMonth);
           CurrentDatabase.AddInParameter(dbcommand, "@company", DbType.String, company);
           CurrentDatabase.ExecuteNonQuery(dbcommand);

       }
       public void UpdateBillInstrument()
       {
           DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("UpdateBillInstrument");
           dbcommand.CommandTimeout = 0;
           CurrentDatabase.ExecuteNonQuery(dbcommand);

       }

       public string GenReportDataBySalesGroup(string sqlwhere)
       {
           DbCommand dbcommand = CurrentDatabase.GetStoredProcCommand("GenReportDataBySalesGroup");
           CurrentDatabase.AddInParameter(dbcommand, "@sqlwhere", DbType.String, sqlwhere);
           CurrentDatabase.AddOutParameter(dbcommand, "@tblname", DbType.String, 30);
           CurrentDatabase.ExecuteNonQuery(dbcommand);
           return dbcommand.Parameters["@tblname"].Value.ToString();
       }

    }
}
